﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE   PROCEDURE [dbo].[PagingRecord]
    ( 
    @PageIndex int,--页号,从0开始
    @PageSize int,--页尺寸
    @OrderField varchar(100),--排序字段及类型(多个条件用逗号分开)如：JobID DESC,Checkintime
    @TableName varchar(500),--表名或视图表 
    @StrWhere varchar(2000),--条件 
    @FieldList varchar(2000),--欲选择字段列表，如 B.aid as aid,U.cname as cname
	@FieldOutPut varchar(2000),--最终输出到datatable的字段,单个表的情况下等于FieldList,多个表的情况下自定,如aid,cname
    @DoCount  AS bit=1-- 0值返回记录总数, 非 0 值则返回记录
    ) 
AS 

BEGIN TRAN

DECLARE @SqlQuery varchar(4000)

IF @DoCount<>0
    Goto GetCount
Else
    Goto GetSearch



GetCount:--返回记录总数
    DECLARE @SearchSql AS Nvarchar(4000)
    SET @SearchSql= 'SELECT Count(*) AS Total FROM '+@TableName+' WHERE '+@StrWhere
    exec sp_executesql @SearchSql
    --print @SearchSql 
COMMIT TRAN
    return

GetSearch:
    
    SET @SqlQuery='SELECT '+@FieldOutPut+'
    FROM (SELECT row_number() over(ORDER BY '+@OrderField+') as rownum, 
            '+@FieldList+'
          FROM '+@TableName+' WHERE '+@StrWhere +') as temp
    WHERE rownum BETWEEN ('+cast(@PageIndex as varchar)+'-1)*'+cast(@PageSize as varchar)+'+1 and '+cast(@PageIndex as varchar)+'*'+cast(@PageSize as varchar) + ' ORDER BY '+@OrderField
    --print @SqlQuery
    SET NOCOUNT ON
    execute(@SqlQuery)
    SET NOCOUNT OFF


COMMIT TRAN


